# coding=utf-8
from openpyxl import  load_workbook
from datetime import datetime, date, timedelta
import os
import random

yesterday = (date.today() + timedelta(days = -1)).strftime("%m%d")    # 昨天日期
today = (date.today() + timedelta()).strftime("%m%d") # 今天日期

today_month = (date.today() + timedelta()).strftime("%m") # 今天日期
today_day = (date.today() + timedelta()).strftime("%d") # 今天日期

todaystr = "%s月%s日" % (today_month,today_day)

yesterdayfilename = 'testfile%s.xlsx' % yesterday
todayfilename = 'testfile%s.xlsx' % today

if os.path.exists('%s' % yesterdayfilename) is False:
    print("昨日%s文件不存在！" % yesterdayfilename)
    exit(0)

workbook = load_workbook(filename=yesterdayfilename)
#获取表格
sheet = workbook['Sheet1']
#获取某个单元格
cellA2 = sheet['A2']
cellB2 = sheet['B2']
cellC2 = sheet['C2']
cellD2 = sheet['D2']
cellE2 = sheet['E2']

newvalueB2 = cellB2.value + random.randint(500,2000)
newvalueC2 = 3
newvalueD2 = cellD2.value + round(random.uniform(0,1),1)
newvalueE2 = cellE2.value + random.randint(1,10)

sheet['A2'] = todaystr
sheet['B2'] = newvalueB2
sheet['C2'] = newvalueC2
sheet['D2'] = newvalueD2
sheet['E2'] = newvalueE2

workbook.save(filename=todayfilename)

print('%s文件生成成功！' % todayfilename)
